package com.gack.business.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.gack.business.vo.CertificationNotPassedEnterpriseDetailVO;
import com.gack.business.vo.CertificationNotPassedEnterpriseItemVO;
import com.gack.business.vo.CertificationPassedEnterpriseDetailVO;
import com.gack.business.vo.CertificationPassedEnterpriseItemVO;
import com.gack.business.vo.CertificationWaitingEnterpriseDetailVO;
import com.gack.business.vo.CertificationWaitingEnterpriseItemVO;
import com.gack.business.vo.EnterpriseBasicInfoVO;
import com.gack.business.vo.EnterpriseItemVO;
import com.gack.business.vo.UserItemVO;

/**
 * 
 * @author ws
 * 2018-6-13
 */
@Repository
public class EnterpriseDao {

	@Autowired
	private EntityManager em;
	
	/**
	 * 根据公司状态和输入串,查询公司ItemVO集合
	 * @param page 所请求的页数  从0开始
	 * @param size 每页显示记录数量
	 * @param status 公司认证状态 0:未认证 1:已认证
	 * @param input 输入匹配串
	 * @return enterpriseItemList:公司ItemVO集合  total:可以查询出的总数量
	 */
	public Map<String, Object> findEnterpriseItemVOListByStatusAndName(Integer page, Integer size, String status, String input){
		Map<String, Object> map = new HashMap<>();
		
		String jpql = "select e.id, e.name,"
				+ " case (select u.is_check from user u where u.id=e.creator)"
				+ " when 1 then (select u.real_name from user u where u.id=e.creator)"
				+ " when 0 then (select u.nickname from user u where u.id=e.creator)"
				+ " end as creator_name_for_show,"
				+ " e.number_range, e.current_number, e.industry, e.location, e.status, e.create_time"
				+ " from enterprise e"
				+ " where e.name like :input"
				+ " and e.status in ";
		
		if(status.equals("0")){
			jpql += "('0', '2', '3')";
		}else{
			jpql += "('1')";
		}
		jpql += " order by e.create_time DESC";
		
		//查询总数量
		Query query = em.createNativeQuery(jpql, EnterpriseItemVO.class);
		query.setParameter("input", "%" + input + "%");
		map.put("total", query.getResultList().size());
		
		//查询分页返回值
		jpql += " limit " + page * size + ", " + size;
		Query query1 = em.createNativeQuery(jpql, EnterpriseItemVO.class);
		query1.setParameter("input", "%" + input + "%");
		
		List<EnterpriseItemVO> enterpriseItemVOList = query1.getResultList();
		map.put("enterpriseItemList", enterpriseItemVOList);
		
		return map;
	}
	
	/**
	 * 根据公司状态和输入串,查询公司ItemVO集合
	 * @param page 所请求的页数  从0开始
	 * @param size 每页显示记录数量
	 * @param status 公司认证状态 0:未认证 1:已认证
	 * @param input 输入匹配串
	 * @return enterpriseItemList:公司ItemVO集合  total:可以查询出的总数量
	 */
	public Map<String, Object> findEnterpriseItemVOListByStatusAndCreatorName(Integer page, Integer size, String status, String input){
		Map<String, Object> map = new HashMap<>();
		
		String jpql = "select e.id, e.name,"
				+ " case (select u.is_check from user u where u.id=e.creator)"
				+ " when 1 then (select u.real_name from user u where u.id=e.creator)"
				+ " when 0 then (select u.nickname from user u where u.id=e.creator)"
				+ " end as creator_name_for_show,"
				+ " e.number_range, e.current_number, e.industry, e.location, e.status, e.create_time"
				+ " from enterprise e"
				+ " where e.creator in (select id from User where nickname like :input or real_name like :input)"
				+ " and e.status in ";
		
		if(status.equals("0")){
			jpql += "('0', '2', '3')";
		}else{
			jpql += "('1')";
		}
		jpql += " order by e.create_time DESC";
		
		//查询总数量
		Query query = em.createNativeQuery(jpql, EnterpriseItemVO.class);
		query.setParameter("input", "%" + input + "%");
		map.put("total", query.getResultList().size());

		//查询分页返回值
		jpql += " limit " + page * size + ", " + size;
		Query query1 = em.createNativeQuery(jpql, EnterpriseItemVO.class);
		query1.setParameter("input", "%" + input + "%");
		
		List<EnterpriseItemVO> enterpriseItemVOList = query1.getResultList();
		map.put("enterpriseItemList", enterpriseItemVOList);
		
		return map;
	}
	
	/**
	 * 根据公司状态和输入串,查询公司ItemVO集合
	 * @param page 所请求的页数  从0开始
	 * @param size 每页显示记录数量
	 * @param status 公司认证状态 0:未认证 1:已认证
	 * @param input 输入匹配串
	 * @return enterpriseItemList:公司ItemVO集合  total:可以查询出的总数量
	 */
	public Map<String, Object> findEnterpriseItemVOListByStatusAndIndustry(Integer page, Integer size, String status, String input){
		Map<String, Object> map = new HashMap<>();
		
		String jpql = "select e.id, e.name,"
				+ " case (select u.is_check from user u where u.id=e.creator)"
				+ " when 1 then (select u.real_name from user u where u.id=e.creator)"
				+ " when 0 then (select u.nickname from user u where u.id=e.creator)"
				+ " end as creator_name_for_show,"
				+ " e.number_range, e.current_number, e.industry, e.location, e.status, e.create_time"
				+ " from enterprise e"
				+ " where e.industry like :input"
				+ " and e.status in ";
		
		if(status.equals("0")){
			jpql += "('0', '2', '3')";
		}else{
			jpql += "('1')";
		}
		jpql += " order by e.create_time DESC";
		
		//查询总数量
		Query query = em.createNativeQuery(jpql, EnterpriseItemVO.class);
		query.setParameter("input", "%" + input + "%");
		map.put("total", query.getResultList().size());
		
		//查询分页返回值
		jpql += " limit " + page * size + ", " + size;
		Query query1 = em.createNativeQuery(jpql, EnterpriseItemVO.class);
		query1.setParameter("input", "%" + input + "%");
		
		List<EnterpriseItemVO> enterpriseItemVOList = query1.getResultList();
		map.put("enterpriseItemList", enterpriseItemVOList);
		
		return map;
	}
	
	/**
	 * 根据公司状态和输入串,查询公司ItemVO集合
	 * @param page 所请求的页数  从0开始
	 * @param size 每页显示记录数量
	 * @param status 公司认证状态 0:未认证 1:已认证
	 * @param input 输入匹配串
	 * @return enterpriseItemList:公司ItemVO集合  total:可以查询出的总数量
	 */
	public Map<String, Object> findEnterpriseItemVOListByStatusAndLocation(Integer page, Integer size, String status, String input){
		Map<String, Object> map = new HashMap<>();
		
		String jpql = "select e.id, e.name,"
				+ " case (select u.is_check from user u where u.id=e.creator)"
				+ " when 1 then (select u.real_name from user u where u.id=e.creator)"
				+ " when 0 then (select u.nickname from user u where u.id=e.creator)"
				+ " end as creator_name_for_show,"
				+ " e.number_range, e.current_number, e.industry, e.location, e.status, e.create_time"
				+ " from enterprise e"
				+ " where e.location like :input"
				+ " and e.status in ";
		
		if(status.equals("0")){
			jpql += "('0', '2', '3')";
		}else{
			jpql += "('1')";
		}
		jpql += " order by e.create_time DESC";
		
		//查询总数量
		Query query = em.createNativeQuery(jpql, EnterpriseItemVO.class);
		query.setParameter("input", "%" + input + "%");
		map.put("total", query.getResultList().size());

		//查询分页返回值
		jpql += " limit " + page * size + ", " + size;
		Query query1 = em.createNativeQuery(jpql, EnterpriseItemVO.class);
		query1.setParameter("input", "%" + input + "%");
				
		List<EnterpriseItemVO> enterpriseItemVOList = query1.getResultList();
		map.put("enterpriseItemList", enterpriseItemVOList);
		
		return map;
	}

	/**
	 * 根据公司id,查询公司基本信息
	 * @param enterpriseId 公司id
	 * @return 
	 */
	public EnterpriseBasicInfoVO findEnterpriseBasicInfoByEnterpriseId(String enterpriseId){
		String jpql = "select e.id, e.name, e.number_range,"
				+ " case (select u.is_check from user u where u.id=e.creator)"
				+ " when 1 then (select u.real_name from user u where u.id=e.creator)"
				+ " when 0 then (select u.nickname from user u where u.id=e.creator)"
				+ " end as creator_name_for_show,"
				+ " e.industry, e.location, e.status, certification_success_time"
				+ " from enterprise e"
				+ " where e.id=:enterpriseId";
		
		Query query = em.createNativeQuery(jpql, EnterpriseBasicInfoVO.class);
		query.setParameter("enterpriseId", enterpriseId);
		if(query.getResultList().size() == 0){
			return null;
		}
		
		return (EnterpriseBasicInfoVO)query.getSingleResult();
	}
	
	/**
	 * 根据公司id,查询该公司下该部门下人员信息
	 * @param enterpriseId 公司id
	 * @param departmentId 部门id
	 * @return
	 */
	public List<UserItemVO> findUserItemListByEnterpriseIdAndDepartmentId(String enterpriseId, List<String> departmentIdList){
		String jpql = "select u.id, u.username, u.portrait, "
				+ " case (select u1.is_check from user u1 where u1.id=u.id)"
				+ " when 1 then (select u1.real_name from user u1 where u1.id=u.id)"
				+ " when 0 then (select u1.nickname from user u1 where u1.id=u.id)"
				+ " end as name_for_show,"
				+ " '' as department_name,"
				+ " (select uedp.department_id"
					+ " from user_enterprise_department_position uedp"
					+ " where uedp.enterprise_id=:enterpriseId"
					+ " and uedp.user_id=u.id) as department_id,"
				+ " case"
				+ " when (select p.name"
					+ " from position p"
					+ " where p.id=(select uedp.position_id"
								   + " from user_enterprise_department_position uedp"
								   + " where uedp.enterprise_id=:enterpriseId"
								   + " and uedp.user_id=u.id)"
					+ " ) is null then ''"
				+ " else (select p.name"
					+ " from position p"
					+ " where p.id=(select uedp.position_id"
								   + " from user_enterprise_department_position uedp"
								   + " where uedp.enterprise_id=:enterpriseId"
								   + " and uedp.user_id=u.id)"
					+ " )"
				+ " end as position"
				+ " from user u"
				+ " where u.id in (select uedp.user_id"
								+ " from user_enterprise_department_position uedp"
								+ " where uedp.enterprise_id=:enterpriseId"
								+ " and uedp.department_id in ";
		
		StringBuilder sb = new StringBuilder("(");
		for(String t : departmentIdList){
			sb.append("'" + t + "', ");
		}
		sb = new StringBuilder(sb.substring(0, sb.length() - ", ".length()));
		sb.append("))");
		
		jpql += sb.toString();
		
		Query query = em.createNativeQuery(jpql, UserItemVO.class);
		query.setParameter("enterpriseId", enterpriseId);
		
		return query.getResultList();
	}
	
	/**
	 * 根据公司id,查询该公司下无部门人员信息
	 * @param enterpriseId 公司id
	 * @return 
	 */
	public List<UserItemVO> findNoDepartmentUserItemListByEnterpriseId(String enterpriseId){
		String jpql = "select u.id, u.username, u.portrait,"
				+ " case (select u1.is_check from user u1 where u1.id=u.id)"
				+ " when 1 then (select u1.real_name from user u1 where u1.id=u.id)"
				+ " when 0 then (select u1.nickname from user u1 where u1.id=u.id)"
				+ " end as name_for_show,"
				+ " '无部门' as department_name,"
				+ " '-1' as department_id,"
				+ " case"
				+ " when (select p.name"
					+ " from position p"
					+ " where p.id=(select uedp.position_id"
								   + " from user_enterprise_department_position uedp"
								   + " where uedp.enterprise_id=:enterpriseId"
								   + " and uedp.user_id=u.id)"
					+ " ) is null then ''"
				+ " else (select p.name"
					+ " from position p"
					+ " where p.id=(select uedp.position_id"
								   + " from user_enterprise_department_position uedp"
								   + " where uedp.enterprise_id=:enterpriseId"
								   + " and uedp.user_id=u.id)"
					+ " )"
				+ " end as position"
				+ " from user u"
				+ "	where u.id in (select uedp.user_id"
								+ " from user_enterprise_department_position uedp"
								+ " where uedp.enterprise_id=:enterpriseId"
								+ " and uedp.department_id is null)";
		
		Query query = em.createNativeQuery(jpql, UserItemVO.class);
		query.setParameter("enterpriseId", enterpriseId);

		return query.getResultList();
	}
	
	/**
	 * 查询认证审核未通过的公司    按审核被拒绝时间降序排序
	 * @param page 请求第几页  从0开始   
	 * @param size 每页显示数量
	 * @return certificationNotPassedEnterpriseItemList:认证审核未通过的公司ItemVO集合  total:认证审核未通过的全部公司数量
	 */
	public Map<String, Object> findCertificationNotPassedEnterpriseItemVO(Integer page, Integer size){
		Map<String, Object> map = new HashMap<>();
		
		String jpql = "select e.id, e.name,"
				+ " case (select u1.is_check from user u1 where u1.id=e.creator)"
				+ " when 1 then (select u1.real_name from user u1 where u1.id=e.creator)"
				+ " when 0 then (select u1.nickname from user u1 where u1.id=e.creator)"
				+ " end as creator_name_for_show,"
				+ " e.certification_submission_time,"
				+ " e.certification_defeat_time,"
				+ " e.rejecting_approval_reason"
				+ " from enterprise e"
				+ " where e.status='3'"
				+ " order by e.certification_defeat_time desc";
				
		//查询总数量
		Query query = em.createNativeQuery(jpql, CertificationNotPassedEnterpriseItemVO.class);
		map.put("total", query.getResultList().size());
		
		//查询分页返回值
		jpql += " limit " + page * size + ", " + size;
		Query query1 = em.createNativeQuery(jpql, CertificationNotPassedEnterpriseItemVO.class);
		
		List<CertificationNotPassedEnterpriseItemVO> list = query1.getResultList();
		map.put("certificationNotPassedEnterpriseItemList", list);
		
		return map;
	}
	
	/**
	 * 查询已通过认证审核的公司    按审核通过时间降序排序
	 * @param page 请求第几页  从0开始   
	 * @param size 每页显示数量
	 * @return  certificationPassedEnterpriseItemList:已通过认证审核的公司ItemVO total:已通过认证审核的全部公司数量
	 */
	public Map<String, Object> findCertificationPassedEnterpriseItemVO(Integer page, Integer size){
		Map<String, Object> map = new HashMap<>();
		
		String jpql = "select e.id, e.name,"
				+ " case (select u1.is_check from user u1 where u1.id=e.creator)"
				+ " when 1 then (select u1.real_name from user u1 where u1.id=e.creator)"
				+ " when 0 then (select u1.nickname from user u1 where u1.id=e.creator)"
				+ " end as creator_name_for_show,"
				+ " e.certification_submission_time,"
				+ " e.certification_success_time,"
				+ " e.number_range,"
				+ " e.industry,"
				+ " e.location"
				+ " from enterprise e"
				+ " where e.status='1'"
				+ " order by e.certification_success_time desc";
				
		//查询总数量
		Query query = em.createNativeQuery(jpql, CertificationPassedEnterpriseItemVO.class);
		map.put("total", query.getResultList().size());
		
		//查询分页返回值
		jpql += " limit " + page * size + ", " + size;
		Query query1 = em.createNativeQuery(jpql, CertificationPassedEnterpriseItemVO.class);
				
		List<CertificationPassedEnterpriseItemVO> list = query1.getResultList();
		map.put("certificationPassedEnterpriseItemList", list);
		
		return map;
	}
	
	/**
	 * 查询已提交认证,但待审核的公司    按审核提交时间降序排序
	 * @param page 请求第几页  从0开始   
	 * @param size 每页显示数量
	 * @return  certificationWaitingEnterpriseItemList:查询已提交认证,但待审核的公司ItemVO total:查询已提交认证,但待审核的全部公司数量
	 */
	public Map<String, Object> findCertificationWaitingEnterpriseItemVO(Integer page, Integer size){
		Map<String, Object> map = new HashMap<>();
		
		String jpql = "select e.id, e.name,"
				+ " case (select u1.is_check from user u1 where u1.id=e.creator)"
				+ " when 1 then (select u1.real_name from user u1 where u1.id=e.creator)"
				+ " when 0 then (select u1.nickname from user u1 where u1.id=e.creator)"
				+ " end as creator_name_for_show,"
				+ " e.certification_submission_time,"
				+ " e.number_range,"
				+ " e.industry,"
				+ " e.location"
				+ " from enterprise e"
				+ " where e.status='2'"
				+ " order by e.certification_submission_time desc";
				
		//查询总数量
		Query query = em.createNativeQuery(jpql, CertificationWaitingEnterpriseItemVO.class);
		map.put("total", query.getResultList().size());
		
		//查询分页返回值
		jpql += " limit " + page * size + ", " + size;
		Query query1 = em.createNativeQuery(jpql, CertificationWaitingEnterpriseItemVO.class);
				
		List<CertificationWaitingEnterpriseItemVO> list = query1.getResultList();
		map.put("certificationWaitingEnterpriseItemList", list);
		
		return map;
	}
	
	/**
	 * 根据公司id,查询认证未通过公司详细信息
	 * @param enterpriseId
	 * @return detail:认证未通过公司详细信息
	 */
	public CertificationNotPassedEnterpriseDetailVO findCertificationNotPassedEnterpriseDetailVO(String enterpriseId){
		String jpql = "select e.id, e.name,"
				+ " case (select u1.is_check from user u1 where u1.id=e.creator)"
				+ " when 1 then (select u1.real_name from user u1 where u1.id=e.creator)"
				+ " when 0 then (select u1.nickname from user u1 where u1.id=e.creator)"
				+ " end as creator_name_for_show,"
				+ " e.certification_submission_time,"
				+ " e.certification_defeat_time,"
				+ " e.rejecting_approval_reason,"
				+ " e.certified_name,"
				+ " e.contract_name,"
				+ " e.unified_social_credit_code"
				+ " from enterprise e"
				+ " where e.status='3'"
				+ " and e.id=:enterpriseId";
				
		
		Query query = em.createNativeQuery(jpql, CertificationNotPassedEnterpriseDetailVO.class);
		query.setParameter("enterpriseId", enterpriseId);
		if(query.getResultList().size() == 0){
			return null;
		}
		
		return (CertificationNotPassedEnterpriseDetailVO)query.getSingleResult();
	}
	
	/**
	 * 根据公司id,查询认证已通过公司详细信息
	 * @param enterpriseId
	 * @return detail:认证已通过公司详细信息
	 */
	public CertificationPassedEnterpriseDetailVO findCertificationPassedEnterpriseDetailVO(String enterpriseId){
		String jpql = "select e.id, e.name,"
				+ " case (select u1.is_check from user u1 where u1.id=e.creator)"
				+ " when 1 then (select u1.real_name from user u1 where u1.id=e.creator)"
				+ " when 0 then (select u1.nickname from user u1 where u1.id=e.creator)"
				+ " end as creator_name_for_show,"
				+ " e.certification_submission_time,"
				+ " e.certification_success_time,"
				+ " e.number_range,"
				+ " e.industry,"
				+ " e.location,"
				+ " e.certified_name,"
				+ " e.contract_name,"
				+ " e.unified_social_credit_code,"
				+ " e.positive_id_card,"
				+ " e.negative_id_card,"
				+ " e.business_license,"
				+ " e.administrator_job_certificate,"
				+ " e.detailed_address"
				+ " from enterprise e"
				+ " where e.status='1'"
				+ " and e.id=:enterpriseId";
				
		
		Query query = em.createNativeQuery(jpql, CertificationPassedEnterpriseDetailVO.class);
		query.setParameter("enterpriseId", enterpriseId);
		if(query.getResultList().size() == 0){
			return null;
		}
		
		return (CertificationPassedEnterpriseDetailVO)query.getSingleResult();
	}
	
	/**
	 * 查询已提交认证,但待审核的公司详细信息
	 * @param enterpriseId 请求第几页  从0开始   
	 * @return  detail:待审核的公司详细信息
	 */
	public CertificationWaitingEnterpriseDetailVO findCertificationWaitingEnterpriseDetailVO(String enterpriseId){
		String jpql = "select e.id, e.name,"
				+ " case (select u1.is_check from user u1 where u1.id=e.creator)"
				+ " when 1 then (select u1.real_name from user u1 where u1.id=e.creator)"
				+ " when 0 then (select u1.nickname from user u1 where u1.id=e.creator)"
				+ " end as creator_name_for_show,"
				+ " e.certification_submission_time,"
				+ " e.certification_success_time,"
				+ " e.number_range,"
				+ " e.industry,"
				+ " e.location,"
				+ " e.certified_name,"
				+ " e.contract_name,"
				+ " e.unified_social_credit_code,"
				+ " e.positive_id_card,"
				+ " e.negative_id_card,"
				+ " e.business_license,"
				+ " e.administrator_job_certificate,"
				+ " e.detailed_address"
				+ " from enterprise e"
				+ " where e.status='2'"
				+ " and e.id=:enterpriseId";
				
		
		Query query = em.createNativeQuery(jpql, CertificationWaitingEnterpriseDetailVO.class);
		query.setParameter("enterpriseId", enterpriseId);
		if(query.getResultList().size() == 0){
			return null;
		}
		
		return (CertificationWaitingEnterpriseDetailVO)query.getSingleResult();
	}
	
}
